from openpyxl import Workbook

from openpyxl.chart import (
    BarChart,
    StockChart,
    Reference,
    Series,
)
from openpyxl.chart.axis import DateAxis, ChartLines
from openpyxl.chart.updown_bars import UpDownBars

# 创建工作表
wb = Workbook()
ws = wb.active

# 准备数据
rows = [
    ['Date', 'Volume', 'Open', 'High', 'Low', 'Close'],
    ['2015-01-01', 20000, 26.2, 27.20, 23.49, 25.45, ],
    ['2015-01-02', 10000, 25.45, 25.03, 19.55, 23.05, ],
    ['2015-01-03', 15000, 23.05, 24.46, 20.03, 22.42, ],
    ['2015-01-04', 2000, 22.42, 23.97, 20.07, 21.90, ],
    ['2015-01-05', 12000, 21.9, 23.65, 19.50, 21.51, ],
]

# 向工作表中追加数据
for row in rows:
    ws.append(row)

# High-low-close
# 创建股票图
c1 = StockChart()
# 标签
labels = Reference(ws, min_col=1, min_row=2, max_row=6)
# 数据
data = Reference(ws, min_col=4, max_col=6, min_row=1, max_row=6)
# 添加数据
c1.add_data(data, titles_from_data=True)
# 设置分类标签
c1.set_categories(labels)
# 遍历每个序列,设置线不填充
for s in c1.series:
    s.graphicalProperties.line.noFill = True
    # marker for close
    # 标记点
    s.marker.symbol = "dot"
    s.marker.size = 5

# 标题
c1.title = "最高价-最低价-收盘价"

# 高低线=折线图
c1.hiLowLines = ChartLines()

# Excel is broken and needs a cache of values in order to display hiLoLines :-/
# 由于Excel中的错误，仅当数据序列中的至少一个具有一些虚拟值时，才会显示高/低行。可以使用以下技巧来做到这一点：
from openpyxl.chart.data_source import NumData, NumVal

pts = [NumVal(idx=i) for i in range(len(data) - 1)]
cache = NumData(pt=pts)
c1.series[-1].val.numRef.numCache = cache

# 将图表添加到工作表
ws.add_chart(c1, "A10")

# Open-high-low-close
# 第二张股票图
c2 = StockChart()
data = Reference(ws, min_col=3, max_col=6, min_row=1, max_row=6)
c2.add_data(data, titles_from_data=True)
c2.set_categories(labels)
for s in c2.series:
    s.graphicalProperties.line.noFill = True
c2.hiLowLines = ChartLines()
c2.upDownBars = UpDownBars()
c2.title = "开盘价-最高价-最低价-收盘价"

# add dummy cache
c2.series[-1].val.numRef.numCache = cache

ws.add_chart(c2, "G10")

# Create bar chart for volume
# 为成交量创建条形图
bar = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=6)
bar.add_data(data, titles_from_data=True)
bar.set_categories(labels)

from copy import deepcopy

# Volume-high-low-close
b1 = deepcopy(bar)
c3 = deepcopy(c1)
c3.y_axis.majorGridlines = None
c3.y_axis.title = "Price"
b1.y_axis.axId = 20
b1.z_axis = c3.y_axis
b1.y_axis.crosses = "max"
b1 += c3

c3.title = "最高价,最低价,收盘价和成交量"

ws.add_chart(b1, "A27")

## Volume-open-high-low-close
b2 = deepcopy(bar)
c4 = deepcopy(c2)
c4.y_axis.majorGridlines = None
c4.y_axis.title = "Price"
b2.y_axis.axId = 20
b2.z_axis = c4.y_axis
b2.y_axis.crosses = "max"
b2 += c4

ws.add_chart(b2, "G27")

wb.save("stock.xlsx")
